CREATE trigger markDish
    on history_order_have
    after update
    as
    declare
        @AvgMark1 decimal(3,2),  --低于0.5的订单平均分
        @AvgMark2 decimal(3,2),
        @AvgMark3 decimal(3,2),  --高于4.5的订单平均分
        @AvgMark decimal(3,2),  --最终评分
        @num1 int,  --低于0.5的订单数量
        @num2 int,
        @num3 int,  --高于4.5的订单数量
        @DishNumber varchar(20),
        @Mark decimal(3,2)

    select @DishNumber = dish_number from inserted
    select @Mark = order_dish_grade from inserted


    select @AvgMark1 = AVG(order_dish_grade),@num1 = COUNT(dish_number)
    from history_order_have
    where order_dish_grade<0.5 and order_dish_grade is not null and dish_number = @DishNumber


    select @AvgMark2 = AVG(order_dish_grade),@num2 = COUNT(dish_number)
    from history_order_have
    where order_dish_grade>=0.5 and order_dish_grade<=4.5 and order_dish_grade is not null and dish_number = @DishNumber


    select @AvgMark3 = AVG(order_dish_grade),@num3 = COUNT(dish_number)
    from history_order_have
    where order_dish_grade>4.5 and order_dish_grade is not null and dish_number = @DishNumber

    --评分值域约束
    if @Mark < 0 or @Mark > 5 begin
        rollback
    end
    else begin
        if(@num1 = 0) select @AvgMark1 = 0
        if(@num2 = 0) select @AvgMark2 = 0
        if(@num3 = 0) select @AvgMark3 = 0
        if(@num1+@num2+@num3<50)begin --总数量很少时，适当降低低档评分权重
        update dish
        set dish_grade = (@AvgMark1*@num1*0.5+@AvgMark2*@num2+@AvgMark3*@num3)/(@num1*0.5+@num2+@num3)
        where dish_number = @DishNumber
        end
        else if(@num1+@num2+@num3<5000)begin --总数量适中时，适当增加中间档评分权重
        update dish
        set dish_grade = (@AvgMark1*@num1*0.75+@AvgMark2*@num2+@AvgMark3*@num3*0.8)/(@num1*0.75+@num2+@num3*0.8)
        where dish_number = @DishNumber
        end
        else begin
            update dish --总数量很多时，则不加干预
            set dish_grade = (@AvgMark1*@num1+@AvgMark2*@num2+@AvgMark3*@num3)/(@num1+@num2+@num3)
            where dish_number = @DishNumber
        end

    end
go

